﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Database
{
    public class DatabaseInfo
    {
        #region 数据库信息初始化

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private readonly string _connectString;

        /// <summary>
        /// 建立数据库的连接
        /// </summary>
        public SqlConnection SqlConn;

        /// <summary>
        /// 初始化数据库属性
        /// </summary>
        public DatabaseInfo()
        {
            string Ip = "127.0.0.1";
            string DbName = "TMIS01";
            string DbUser = "sa";
            string DbPassword = "douzhihao";

            _connectString = "Server=" + Ip + @"; Integrated Security=false; Database=" + DbName +
                             @"; Uid=" + DbUser + @"; Pwd=" + DbPassword +
                             @";MultipleActiveResultSets=True";
        }

        #endregion

        #region 系统参数初始化

        /// <summary>
        /// 用户名
        /// </summary>
        public static string UserName;

        #endregion

        /// <summary>
        /// 打开数据连接
        /// </summary>
        public void ConnectDataBase()
        {
            try
            {
                //打开数据库
                if (SqlConn == null)
                {
                    SqlConn = new SqlConnection(_connectString);
                }

                if (SqlConn.State == ConnectionState.Open)
                {
                    return;
                }

                SqlConn.Close();
                SqlConn.Open();
            }
            catch (Exception)
            {
                // ignored
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseDataBase()
        {
            try
            {
                if (SqlConn != null)
                {
                    if (SqlConn.State == ConnectionState.Open)
                    {
                        SqlConn.Close();
                    }

                    SqlConn.Dispose();
                    SqlConn = null;
                }
            }
            catch (Exception)
            {
                // ignored
            }
        }

        /// <summary>
        /// 根据sql语句进行数据库的查询,并返回表格的某一列
        /// </summary>
        /// <param name="sqlCommand"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public List<string> ColumnDataQuery(string sqlCommand,string columnName)
        {
            try
            {
                List<string> result = new List<string>();
                ConnectDataBase();
                SqlCommand command = SqlConn.CreateCommand();
                command.CommandText = sqlCommand;
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    var type = reader[columnName].ToString();
                    result.Add(type);
                }
                reader.Close();
                CloseDataBase();
                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 根据sql语句对数据库查询并返回特定的表格
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable TableDataQuery(string sql, string tableName)
        {
            ConnectDataBase();
            SqlDataAdapter sda = new SqlDataAdapter(sql, SqlConn);
            DataSet ds = new DataSet();
            sda.Fill(ds, tableName);
            CloseDataBase();
            if (ds.Tables.Count == 0)
            {
                return null;
            }

            return ds.Tables[tableName];
        }

        /// <summary>
        /// 执行指定的sql语句
        /// </summary>
        public void SqlOperator(string sqlCommand)
        {
            try
            {
                ConnectDataBase();
                SqlCommand command = SqlConn.CreateCommand();
                command.CommandText = sqlCommand;
                command.ExecuteReader();
                CloseDataBase();
            }
            catch (Exception)
            {
                // ignored
            }
        }

    }
}